package org.jeecg.modules.goods.goods.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.extern.slf4j.Slf4j;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.aspect.annotation.AutoLog;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.base.controller.JeecgController;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.util.CommonUtils;
import org.jeecg.common.util.UUIDGenerator;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.goods.goods.entity.EbizGoods;
import org.jeecg.modules.goods.goods.entity.Goods;
import org.jeecg.modules.goods.goods.enums.DictFieldEnum;
import org.jeecg.modules.goods.goods.service.IGoodsService;
import org.jeecg.modules.goods.goods.vo.ExportGoodsRequest;
import org.jeecg.modules.goods.goodsconfig.entity.WhGoodsConfig;
import org.jeecg.modules.goods.goodsconfig.service.IWhGoodsConfigService;
import org.jeecg.modules.goods.util.S3Util;
import org.jeecg.modules.oss.entity.OssFile;
import org.jeecg.modules.oss.service.IOssFileService;
import org.jeecg.common.util.MinioUtil;
import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.modules.tplus.service.DatabaseService;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.def.NormalExcelConstants;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.view.JeecgEntityExcelView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.WorkbookUtil;

import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.*;
import java.lang.reflect.Field;

/**
 * @Description: 产品信息1
 * @Author: jeecg-boot
 * @Date: 2025-06-07
 * @Version: V1.0
 */
@Slf4j
@Tag(name = "产品信息")
@RestController
@RequestMapping("/goods/goods")
public class GoodsController extends JeecgController<Goods, IGoodsService> {
	@Autowired
	private IGoodsService goodsService;
	@Autowired
	private S3Util s3Util;
	@Autowired
	private IOssFileService ossFileService;
	@Autowired
	private IWhGoodsConfigService whGoodsConfigService;
	@Autowired
	private DatabaseService databaseService;
	@Autowired
	private ISysBaseAPI sysBaseAPI;

    @Value("${jeecg.path.upload}")
    private String upLoadPath;


	/**
	 * 分页列表查询
	 *
	 * @param goods
	 * @param pageNo
	 * @param pageSize
	 * @param req
	 * @return
	 */
	@AutoLog(value = "产品信息-分页列表查询")
	@Operation(summary = "产品信息-分页列表查询")
	@GetMapping(value = "/list")
	public Result<?> queryPageList(Goods goods,
			@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
			@RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
			HttpServletRequest req) {
		QueryWrapper<Goods> queryWrapper = QueryGenerator.initQueryWrapper(goods, req.getParameterMap());

		// 处理JSON字段查询条件
		processJsonFieldQueries(queryWrapper, req.getParameterMap());

		Page<Goods> page = new Page<>(pageNo, pageSize);
		IPage<Goods> pageList = goodsService.page(page, queryWrapper);

		// 处理返回数据中的图片和视频URL，添加minio_url前缀
		List<Goods> records = pageList.getRecords();
		for (Goods record : records) {
			processMediaUrls(record, req);
		}

		return Result.OK(pageList);
	}

	/**
	 * 处理商品中的图片和视频URL，为otherPics和otherVideos添加minio_url前缀
	 *
	 * @param goods 商品对象
	 * @param request HTTP请求对象
	 */
	private void processMediaUrls(Goods goods, HttpServletRequest request) {
		try {
			String minioUrl = MinioUtil.getDynamicMinioUrl(request);
			if (minioUrl == null || minioUrl.isEmpty()) {
				return;
			}

			// 处理goodsPic中的otherPics
			if (goods.getGoodsPic() != null) {
				Object updatedGoodsPic = processJsonUrls(goods.getGoodsPic(), minioUrl, "otherPics");
				if (updatedGoodsPic != null) {
					goods.setGoodsPic(updatedGoodsPic);
				}
			}

			// 处理goodsVideo中的otherVideos
			if (goods.getGoodsVideo() != null) {
				Object updatedGoodsVideo = processJsonUrls(goods.getGoodsVideo(), minioUrl, "otherVideos");
				if (updatedGoodsVideo != null) {
					goods.setGoodsVideo(updatedGoodsVideo);
				}
			}
		} catch (Exception e) {
			log.error("处理媒体URL失败: {}", e.getMessage(), e);
		}
	}

	/**
	 * 处理JSON对象中的URL字段，添加minio_url前缀
	 *
	 * @param jsonObj   JSON对象
	 * @param minioUrl  minio URL前缀
	 * @param fieldName 要处理的字段名
	 * @return 更新后的JSON对象
	 */
	private Object processJsonUrls(Object jsonObj, String minioUrl, String fieldName) {
		try {
			if (jsonObj == null) {
				return null;
			}

			Map<String, Object> jsonMap;
			boolean isStringInput = false;

			if (jsonObj instanceof String) {
				jsonMap = JSON.parseObject((String) jsonObj, Map.class);
				isStringInput = true;
			} else {
				jsonMap = JSON.parseObject(JSON.toJSONString(jsonObj), Map.class);
			}

			if (jsonMap != null && jsonMap.containsKey(fieldName)) {
				Object fieldValue = jsonMap.get(fieldName);
				boolean updated = false;

				// 保存原始值到新字段
				String originalFieldName = fieldName + "_original";
				jsonMap.put(originalFieldName, fieldValue);

				if (fieldValue instanceof List) {
					@SuppressWarnings("unchecked")
					List<String> urlList = (List<String>) fieldValue;
					for (int i = 0; i < urlList.size(); i++) {
						String url = urlList.get(i);
						if (url != null && !url.startsWith("http") && !url.startsWith(minioUrl)) {
							urlList.set(i, minioUrl + url);
							updated = true;
						}
					}
					if (updated) {
						jsonMap.put(fieldName, urlList);
					}
				} else if (fieldValue instanceof String) {
					String fieldStr = (String) fieldValue;
					if (fieldStr != null && !fieldStr.trim().isEmpty()) {
						// 检查是否为逗号分隔的多个URL
						if (fieldStr.contains(",")) {
							String[] urls = fieldStr.split(",");
							StringBuilder updatedUrls = new StringBuilder();
							for (int i = 0; i < urls.length; i++) {
								String url = urls[i].trim();
								if (!url.isEmpty()) {
									if (!url.startsWith("http") && !url.startsWith(minioUrl)) {
										url = minioUrl + url;
										updated = true;
									}
									if (i > 0) {
                                        updatedUrls.append(",");
                                    }
									updatedUrls.append(url);
								}
							}
							if (updated) {
								jsonMap.put(fieldName, updatedUrls.toString());
							}
						} else {
							// 单个URL处理
							if (!fieldStr.startsWith("http") && !fieldStr.startsWith(minioUrl)) {
								jsonMap.put(fieldName, minioUrl + fieldStr);
								updated = true;
							}
						}
					}
				}

				if (updated) {
					// 返回更新后的对象
					if (isStringInput) {
						return JSON.toJSONString(jsonMap);
					} else {
						return jsonMap;
					}
				}
			}

			return null; // 没有更新则返回null
		} catch (Exception e) {
			log.error("处理JSON URL失败: {}", e.getMessage(), e);
			return null;
		}
	}

	/**
	 * 添加
	 *
	 * @param goods
	 * @return
	 */
	@AutoLog(value = "产品信息-添加")
	@Operation(summary = "产品信息-添加")
	@PostMapping(value = "/add")
	public Result<?> add(@RequestBody Goods goods) {
		if (StringUtils.isBlank(goods.getGoodsCode())) {
			return Result.error("产品编码不能为空，请先保存产品基本信息！");
		}
		Goods good = goodsService.saveEntity(goods);
		return Result.OK("添加成功！", good);
	}

	/**
	 * 产品图片处理
	 *
	 * @param ebizGoods
	 * @return
	 */
	@AutoLog(value = "产品图片处理")
	@Operation(summary = "产品图片处理")
	@PostMapping(value = "/handleGoodsPic")
	public Result<?> handleGoodsPic(@RequestBody EbizGoods ebizGoods) {
		goodsService.handleGoodsPic(ebizGoods);
		return Result.OK("操作成功！");
	}


	/**
	 * 产品T+图片处理
	 *
	 * @return
	 */
	@AutoLog(value = "产品T+图片处理")
	@Operation(summary = "产品T+图片处理")
	@PostMapping(value = "/handlePlusGoodsPic")
	public Result<?> handlePlusGoodsPic(@RequestBody EbizGoods ebizGoods) {
		String sql = "select code,imageFile from AA_InventoryEntity where (imageFile is not null or imageFile <> '') ";
		if(StringUtils.isNotBlank(ebizGoods.getGoodsCode())){
			sql += " and code = '" + ebizGoods.getGoodsCode() + "'";
		}
		List<Map<String, Object>> list = databaseService.queryFromSecondaryDatabase(sql);
//		List<Map<String, Object>> list = new ArrayList<>();
//		Map<String, Object> map = new HashMap<>();
//		map.put("code",ebizGoods.getGoodsCode());
//		map.put("imageFile","1840d89c-8d81-4f34-b870-533000b84b9e.jpg:default;74d75056-ce51-4f93-802f-e521793fe764.jpg;3e5c2309-79b6-47ae-9c99-085534deacd7.jpg;53fd07b8-5f72-497d-9bbf-9b50456a9cef.jpg;ccc1aa3b-a8bd-46b6-ab7c-c9cc614517a8.jpg;");
//		list.add(map);
		//        sql = "select FileName,FilePath from eap_UserImageInfo where VoucherName = 'Inventory' ";
//        List<Map<String, Object>> picList = databaseService.queryFromSecondaryDatabase(sql);
        List<Map<String, Object>> picList = new ArrayList<>();
		goodsService.handlePlusGoodsPic(ebizGoods,list,picList);
		return Result.OK("操作成功！");
	}

	/**
	 * 编辑
	 *
	 * @param goods
	 * @return
	 */
	@AutoLog(value = "产品信息-编辑")
	@Operation(summary = "产品信息-编辑")
	@RequestMapping(value = "/edit", method = { RequestMethod.PUT, RequestMethod.POST })
	public Result<?> edit(@RequestBody Goods goods) {
		if (StringUtils.isBlank(goods.getGoodsCode())) {
			return Result.error("产品编码不能为空，请先保存产品基本信息！");
		}
		Goods good = goodsService.updateEntityById(goods);
		return Result.OK("编辑成功!", good);
	}

	/**
	 * 通过id删除
	 *
	 * @param id
	 * @return
	 */
	@AutoLog(value = "产品信息-通过id删除")
	@Operation(summary = "产品信息-通过id删除")
	@DeleteMapping(value = "/delete")
	public Result<?> delete(@RequestParam(name = "id", required = true) String id) {
		goodsService.removeById(id);
		return Result.OK("删除成功!");
	}

	/**
	 * 批量删除
	 *
	 * @param ids
	 * @return
	 */
	@AutoLog(value = "产品信息-批量删除")
	@Operation(summary = "产品信息-批量删除")
	@DeleteMapping(value = "/deleteBatch")
	public Result<?> deleteBatch(@RequestParam(name = "ids", required = true) String ids) {
		this.goodsService.removeByIds(Arrays.asList(ids.split(",")));
		return Result.OK("批量删除成功！");
	}

	/**
	 * 通过id查询
	 *
	 * @param id
	 * @return
	 */
	@AutoLog(value = "产品信息-通过id查询")
	@Operation(summary = "产品信息-通过id查询")
	@GetMapping(value = "/queryById")
	public Result<?> queryById(@RequestParam(name = "id", required = true) String id) {
		Goods goods = goodsService.getById(id);
		return Result.OK(goods);
	}

	@AutoLog(value = "产品配置信息-通过产品分类")
	@Operation(summary = "产品配置信息-通过产品分类")
	@GetMapping(value = "/queryByCategory")
	public Result<?> queryByCategory(@RequestParam(name = "category") String category) {
		LambdaQueryWrapper<WhGoodsConfig> query = new LambdaQueryWrapper<>();
		query.eq(WhGoodsConfig::getGoodsCategory, category);
		query.orderByAsc(WhGoodsConfig::getCreateTime);
		List<WhGoodsConfig> goodsConfigs = whGoodsConfigService.list(query);
		return Result.OK(goodsConfigs);
	}

	/**
	 * 导出excel
	 *
	 * @param request
	 * @param goods
	 */
	@RequestMapping(value = "/exportXls")
	public ModelAndView exportXls(HttpServletRequest request, Goods goods) {
		return super.exportXls(request, goods, Goods.class, "产品信息");
	}

	/**
	 * 按字段导出excel（支持是否导出图片）
	 *
	 * 前端传入导出的字段列表和是否导出图片标记；
	 * 字段名应为实体属性名，例如：goodsCode, goodsName, coverPic 等。
	 */
	@AutoLog(value = "产品信息-按字段导出")
	@Operation(summary = "产品信息-按字段导出")
	@PostMapping(value = "/exportByFields")
	public void exportByFields(@RequestBody ExportGoodsRequest req, Goods goods, HttpServletRequest request, HttpServletResponse response) throws IOException {
		// 标题处理
		String title = (req.getTitle() == null || req.getTitle().trim().isEmpty()) ? "产品信息" : req.getTitle().trim();
		// 规范化字段（支持 snake_case -> camelCase）
		List<String> inputFields = req.getFields();
		if (inputFields == null || inputFields.isEmpty()) {
			// 如果没有指定字段，则使用默认导出
			exportDefaultExcel(goods, request, response, title);
			return;
		}
		List<String> normalized = new ArrayList<>();
		for (String f : inputFields) {
			String nf = normalizeFieldName(f);
			if (nf != null && !nf.isEmpty()) {
				normalized.add(nf);
			}
		}

		// 图片字段控制
		Boolean includeImages = req.getIncludeImages();
		List<String> imageFields = Arrays.asList("coverPic");
		if (includeImages == null || !includeImages) {
			normalized.removeAll(imageFields);
		}

		if (normalized.isEmpty()) {
			exportDefaultExcel(goods, request, response, title);
			return;
		}

		// 组装查询条件及选中项过滤
		QueryWrapper<Goods> queryWrapper = QueryGenerator.initQueryWrapper(goods, request.getParameterMap());
		String selections = request.getParameter("selections");
		if (oConvertUtils.isNotEmpty(selections)) {
			List<String> selectionList = Arrays.asList(selections.split(","));
			queryWrapper.in("id", selectionList);
		}

        // 获取数据并进行JSON与图片字段预处理
        List<Goods> exportList = goodsService.list(queryWrapper);
        boolean hasLocalImage = false;

        // 创建导出用的Map列表，用于存储所有字段值（包括嵌套字段）
        List<Map<String, Object>> exportMapList = new ArrayList<>();
        // 存储图片信息，用于后续处理
        Map<Integer, Map<String, String>> imageInfoMap = new HashMap<>();

        int rowIndex = 1; // 数据行索引，从1开始（第0行是标题）
        for (Goods g : exportList) {
            // 处理minio前缀到视频/图片集合字段
            processMediaUrls(g, request);

            // 处理嵌套在JSON字段中的属性提取
            Map<String, Object> extraFields = processNestedFields(g, new HashSet<>(normalized));

            // 创建用于导出的Map对象
            Map<String, Object> exportMap = convertGoodsToMap(g, normalized, extraFields);

            // 封面图清洗与回退
            if (normalized.contains("coverPic")) {
                String cover = cleanUrlString(g.getCoverPic());
                if (StringUtils.isBlank(cover)) {
                    cover = extractCoverPicFromGoodsPic(g.getGoodsPic());
                }
                // 外网URL下载到本地并转换为相对路径，便于Excel嵌入图片
                if (StringUtils.isNotBlank(cover) && cover.startsWith("http")) {
                    String rel = saveExternalImageToUploadBase(cover);
                    if (StringUtils.isNotBlank(rel)) {
                        exportMap.put("coverPic", rel);
                        hasLocalImage = true;
                        // 记录图片信息
                        Map<String, String> imageMap = imageInfoMap.computeIfAbsent(rowIndex, k -> new HashMap<>());
                        imageMap.put("coverPic", cover);
                    } else {
                        // 下载失败则仍以URL字符串导出（不会嵌入图片）
                        exportMap.put("coverPic", cover);
                    }
                } else {
                    // 已是本地相对路径，直接用于嵌入图片
                    exportMap.put("coverPic", cover);
                    if (StringUtils.isNotBlank(cover)) {
                        hasLocalImage = true;
                        // 记录图片信息
                        Map<String, String> imageMap = imageInfoMap.computeIfAbsent(rowIndex, k -> new HashMap<>());
                        imageMap.put("coverPic", cover);
                    }
                }
            }

            exportMapList.add(exportMap);
            rowIndex++;
        }

		// 设置响应头
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
		response.setCharacterEncoding("utf-8");
		String fileName = title + ".xlsx";
		response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

		// 创建Excel工作簿
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(title));

		// 创建标题行
		Row headerRow = sheet.createRow(0);
		int cellIndex = 0;
		for (String fieldName : normalized) {
			Cell cell = headerRow.createCell(cellIndex++);
			// 使用自定义标题或默认标题
			String fieldTitle = getFieldTitle(fieldName, req.getFieldTitles(), normalized);
			cell.setCellValue(fieldTitle);
		}

		// 填充数据
		rowIndex = 1;
		Drawing<?> drawing = null;
		if (hasLocalImage && includeImages != null && includeImages) {
			drawing = sheet.createDrawingPatriarch();
		}

		for (Map<String, Object> dataMap : exportMapList) {
			Row dataRow = sheet.createRow(rowIndex);
			for (int i = 0; i < normalized.size(); i++) {
				String fieldName = normalized.get(i);
				Object value = dataMap.get(fieldName);
				Cell cell = dataRow.createCell(i);

				// 如果是图片字段且需要导出图片
				if ("coverPic".equals(fieldName) && hasLocalImage && includeImages != null && includeImages) {
					if (value != null && StringUtils.isNotBlank(value.toString())) {
						// 尝试添加图片
						try {
							String imagePath = value.toString();
							if (imagePath.startsWith("http")) {
								// 网络图片
								addImageToCell(workbook, drawing, cell, imagePath, rowIndex, i);
							} else {
								// 本地图片
								String fullPath = upLoadPath + "/" + imagePath;
								addImageToCell(workbook, drawing, cell, fullPath, rowIndex, i);
							}
						} catch (Exception e) {
							log.warn("添加图片失败: {}", e.getMessage());
							// 添加失败时，仍然显示URL
							cell.setCellValue(value != null ? value.toString() : "");
						}
					}
				} else {
					// 普通字段处理
					if (value != null) {
						if (value instanceof Number) {
							cell.setCellValue(((Number) value).doubleValue());
						} else {
							cell.setCellValue(value.toString());
						}
					}
				}
			}
			rowIndex++;
		}

		// 自动调整列宽
		for (int i = 0; i < normalized.size(); i++) {
			String fieldName = normalized.get(i);
			sheet.autoSizeColumn(i);
			// 如果是图片列，设置固定宽度
			if ("coverPic".equals(fieldName) && hasLocalImage && includeImages != null && includeImages) {
				sheet.setColumnWidth(i, 20 * 256); // 约20个字符宽度
			}
		}

		// 写入响应输出流
		OutputStream outputStream = response.getOutputStream();
		workbook.write(outputStream);
		workbook.close();
		outputStream.close();
    }

	/**
	 * 添加图片到单元格
	 */
	private void addImageToCell(Workbook workbook, Drawing<?> drawing, Cell cell, String imagePath, int rowIndex, int cellIndex) {
		try {
			InputStream is = null;
			if (imagePath.startsWith("http")) {
				// 网络图片
				URL url = new URL(imagePath);
				URLConnection conn = url.openConnection();
				conn.setRequestProperty("User-Agent", "Mozilla/5.0");
				is = conn.getInputStream();
			} else {
				// 本地图片
				java.nio.file.Path path = java.nio.file.Paths.get(imagePath);
				if (java.nio.file.Files.exists(path)) {
					is = java.nio.file.Files.newInputStream(path);
				}
			}

			if (is != null) {
				byte[] bytes = is.readAllBytes();
				is.close();

				// 判断图片类型
				int pictureType = Workbook.PICTURE_TYPE_JPEG;
				if (imagePath.toLowerCase().endsWith(".png")) {
					pictureType = Workbook.PICTURE_TYPE_PNG;
				}

				int pictureIdx = workbook.addPicture(bytes, pictureType);

				CreationHelper helper = workbook.getCreationHelper();
				ClientAnchor anchor = helper.createClientAnchor();
				anchor.setCol1(cellIndex);
				anchor.setRow1(rowIndex);
				anchor.setCol2(cellIndex + 1);
				anchor.setRow2(rowIndex + 1);
				anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

				Picture pict = drawing.createPicture(anchor, pictureIdx);
				pict.resize();

				// 设置单元格为空
				cell.setCellValue("");
			} else {
				cell.setCellValue(imagePath);
			}
		} catch (Exception e) {
			log.warn("添加图片到单元格失败: {}", e.getMessage());
			cell.setCellValue(imagePath);
		}
	}

	/**
	 * 默认Excel导出方法
	 */
	private void exportDefaultExcel(Goods goods, HttpServletRequest request, HttpServletResponse response, String title) throws IOException {
		// 设置响应头
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
		response.setCharacterEncoding("utf-8");
		String fileName = title + ".xlsx";
		response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

		// 获取数据
		QueryWrapper<Goods> queryWrapper = QueryGenerator.initQueryWrapper(goods, request.getParameterMap());
		String selections = request.getParameter("selections");
		if (oConvertUtils.isNotEmpty(selections)) {
			List<String> selectionList = Arrays.asList(selections.split(","));
			queryWrapper.in("id", selectionList);
		}
		List<Goods> exportList = goodsService.list(queryWrapper);

		// 创建Excel工作簿
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(title));

		// 创建标题行（使用Goods类的所有字段）
		Row headerRow = sheet.createRow(0);
		Field[] fields = Goods.class.getDeclaredFields();
		List<String> fieldNames = new ArrayList<>();
		Map<String, String> fieldNameMap = new HashMap<>();
		int cellIndex = 0;
		for (Field field : fields) {
			Excel excelAnnotation = field.getAnnotation(Excel.class);
			if (excelAnnotation != null) {
				String displayName = excelAnnotation.name();
				Cell cell = headerRow.createCell(cellIndex);
				cell.setCellValue(displayName);
				fieldNames.add(field.getName());
				fieldNameMap.put(field.getName(), displayName);
				cellIndex++;
			}
		}

		// 填充数据
		int rowIndex = 1;
		Drawing<?> drawing = sheet.createDrawingPatriarch();
		boolean hasImage = false;
		for (Goods g : exportList) {
			Row dataRow = sheet.createRow(rowIndex);
			cellIndex = 0;
			for (Field field : fields) {
				Excel excelAnnotation = field.getAnnotation(Excel.class);
				if (excelAnnotation != null) {
					try {
						field.setAccessible(true);
						Object value = field.get(g);
						Cell cell = dataRow.createCell(cellIndex);
						// 特殊处理图片字段
						if ("coverPic".equals(field.getName()) && value != null && StringUtils.isNotBlank(value.toString())) {
							hasImage = true;
							// 尝试添加图片
							try {
								String imagePath = value.toString();
								if (imagePath.startsWith("http")) {
									// 网络图片
									addImageToCell(workbook, drawing, cell, imagePath, rowIndex, cellIndex);
								} else {
									// 本地图片
									String fullPath = upLoadPath + "/" + imagePath;
									addImageToCell(workbook, drawing, cell, fullPath, rowIndex, cellIndex);
								}
							} catch (Exception e) {
								log.warn("添加图片失败: {}", e.getMessage());
								// 添加失败时，仍然显示URL
								cell.setCellValue(value != null ? value.toString() : "");
							}
						} else {
							// 普通字段处理
							if (value != null) {
								if (value instanceof Number) {
									cell.setCellValue(((Number) value).doubleValue());
								} else {
									cell.setCellValue(value.toString());
								}
							}
						}
					} catch (IllegalAccessException e) {
						log.warn("获取字段值失败: {}", field.getName(), e);
					}
					cellIndex++;
				}
			}
			rowIndex++;
		}

		// 自动调整列宽
		for (int i = 0; i < fieldNames.size(); i++) {
			String fieldName = fieldNames.get(i);
			if (hasImage && "coverPic".equals(fieldName)) {
				sheet.setColumnWidth(i, 20 * 256); // 图片列固定宽度
			} else {
				sheet.autoSizeColumn(i);
			}
		}

		// 写入响应输出流
		OutputStream outputStream = response.getOutputStream();
		workbook.write(outputStream);
		workbook.close();
		outputStream.close();
	}

    /**
     * 获取字段显示名称
     */
    private String getFieldDisplayName(String fieldName) {
        try {
            Field field = Goods.class.getDeclaredField(fieldName);
            Excel excelAnnotation = field.getAnnotation(Excel.class);
            if (excelAnnotation != null) {
                return excelAnnotation.name();
            }
        } catch (NoSuchFieldException e) {
            // 如果找不到字段，返回字段名本身
            return fieldName;
        }
        return fieldName;
    }

    /**
     * 将Goods对象转换为Map，包含嵌套字段
     *
     * @param goods Goods对象
     * @param normalized 需要导出的字段列表
     * @param extraFields 从JSON中提取的额外字段
     * @return 填充后的Map
     */
    private Map<String, Object> convertGoodsToMap(Goods goods, List<String> normalized, Map<String, Object> extraFields) {
        Map<String, Object> exportMap = new LinkedHashMap<>();

        // 使用反射获取Goods对象的所有字段值
        Field[] fields = Goods.class.getDeclaredFields();

        for (String fieldName : normalized) {
            boolean found = false;

            // 先从Goods对象的字段中查找
            for (Field field : fields) {
                if (field.getName().equals(fieldName)) {
                    try {
                        field.setAccessible(true);
                        Object value = field.get(goods);

                        // 检查是否需要进行字典转换
                        DictFieldEnum dictField = DictFieldEnum.fromFieldName(fieldName);
                        if (dictField != null && value != null) {
                            String dictText = translateDictValue(dictField, value.toString());
                            exportMap.put(fieldName, dictText != null ? dictText : value);
                        } else {
                            exportMap.put(fieldName, value);
                        }

                        found = true;
                        break;
                    } catch (Exception e) {
                        log.warn("获取字段{}的值失败: {}", fieldName, e.getMessage());
                    }
                }
            }

            // 如果在Goods对象中没找到，则从extraFields中查找
            if (!found) {
                Object value = extraFields.get(fieldName);
                // 检查是否需要进行字典转换
                DictFieldEnum dictField = DictFieldEnum.fromFieldName(fieldName);
                if (dictField != null && value != null) {
                    String dictText = translateDictValue(dictField, value.toString());
                    exportMap.put(fieldName, dictText != null ? dictText : value);
                } else {
                    exportMap.put(fieldName, value);
                }
            }
        }

        return exportMap;
    }

    /**
     * 翻译字典值为文本
     *
     * @param dictField 字典字段枚举
     * @param value 字典值
     * @return 字典文本
     */
    private String translateDictValue(DictFieldEnum dictField, String value) {
        try {
            switch (dictField.getDictType()) {
                case DICT:
                    // 数据字典
                    return sysBaseAPI.translateDict(dictField.getTableName(), value);
                case TABLE:
                    // 表字典
                    return sysBaseAPI.translateDictFromTable(
                        dictField.getTableName(),
                        dictField.getTextField(),
                        dictField.getValueField(),
                        value);
                case CATEGORY:
                    // 分类字典
                    // 对于分类字典，我们可能需要特殊处理
                    // 这里简化处理，实际项目中可能需要调用专门的分类字典服务
                    return sysBaseAPI.translateDict("category", value);
                default:
                    return value;
            }
        } catch (Exception e) {
            log.warn("字典翻译失败，字段: {}, 值: {}, 错误: {}", dictField.getFieldName(), value, e.getMessage());
        }
        return null;
    }

    /**
     * 处理嵌套在JSON字段中的属性提取
     * 将goodsInfo、goodsPrice等JSON字段中的属性提取出来，以便导出
     *
     * @param goods Goods对象
     * @param normalized 需要导出的字段列表
     * @return 额外字段映射
     */
    private Map<String, Object> processNestedFields(Goods goods, Set<String> normalized) {
        Map<String, Object> extraFields = new HashMap<>();

        // 定义JSON字段映射关系：字段名 -> 对应的JSON对象
        Map<String, Object> jsonFieldMap = new HashMap<>();
        jsonFieldMap.put("goodsInfo", goods.getGoodsInfo());
        jsonFieldMap.put("goodsPrice", goods.getGoodsPrice());
        jsonFieldMap.put("goodsSale", goods.getGoodsSale());
        jsonFieldMap.put("goodsEnglish", goods.getGoodsEnglish());
        jsonFieldMap.put("goodsDeliver", goods.getGoodsDeliver());
        jsonFieldMap.put("goodsExtend", goods.getGoodsExtend());

        // 遍历所有需要导出的字段
        for (String fieldName : normalized) {
            // 检查该字段是否存在于JSON字段中
            for (Map.Entry<String, Object> entry : jsonFieldMap.entrySet()) {
                Object jsonObject = entry.getValue();
                if (jsonObject != null) {
                    // 从JSON对象中提取对应字段的值
                    Object fieldValue = extractFieldFromJson(jsonObject, fieldName);
                    if (fieldValue != null) {
                        // 将提取的值保存到额外字段映射中
                        extraFields.put(fieldName, fieldValue);
                        break; // 找到后跳出内层循环
                    }
                }
            }
        }

        return extraFields;
    }

    /**
     * 从JSON对象中提取指定字段的值
     *
     * @param jsonObject JSON对象
     * @param fieldName 字段名
     * @return 字段值
     */
    private Object extractFieldFromJson(Object jsonObject, String fieldName) {
        try {
            Map<String, Object> map;
            if (jsonObject instanceof String) {
                map = JSON.parseObject((String) jsonObject, Map.class);
            } else {
                map = JSON.parseObject(JSON.toJSONString(jsonObject), Map.class);
            }
            return map != null ? map.get(fieldName) : null;
        } catch (Exception e) {
            log.warn("解析JSON字段失败: {}", e.getMessage());
            return null;
        }
    }

    /**
     * 将外网图片URL下载到本地上传目录，并返回相对路径（相对于 upLoadPath）。
     * 返回的相对路径用于 Excel 图片嵌入（配合 imageBasePath）。
     */
    private String saveExternalImageToUploadBase(String urlStr) {
        try {
            if (StringUtils.isBlank(urlStr)) { return null; }
            URL url = new URL(urlStr);
            String fileName = UUID.randomUUID().toString();
            String ext = ".jpg";
            String path = url.getPath();
            int idx = path.lastIndexOf('.');
            if (idx > -1 && idx < path.length() - 1) {
                String cand = path.substring(idx).toLowerCase();
                if (cand.matches("\\.(png|jpg|jpeg|gif|bmp)")) {
                    ext = cand;
                }
            }

            // 目录: upLoadPath/export_images
            Path base = Paths.get(upLoadPath);
            Path dir = base.resolve("export_images");
            Files.createDirectories(dir);

            Path dest = dir.resolve(fileName + ext);
            try (InputStream in = url.openStream()) {
                Files.copy(in, dest, StandardCopyOption.REPLACE_EXISTING);
            }
            // 返回相对路径供 Excel 使用
            return "export_images/" + fileName + ext;
        } catch (Exception e) {
            log.warn("下载外网图片失败: {}", e.getMessage());
            return null;
        }
    }

	/**
	 * 通过excel导入数据
	 *
	 * @param request
	 * @param response
	 * @return
	 */
	@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
	public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) {
		return super.importExcel(request, response, Goods.class);
	}

	/**
	 * 商品图片上传
	 *
	 * @return
	 */
	@AutoLog(value = "商品图片上传")
	@Operation(summary = "商品图片上传")
	@PostMapping(value = "/uploadAws")
	public Result<?> uploadAws(@RequestParam("file") MultipartFile file,
			@RequestParam(value = "fileType", required = false) String fileType,
			@RequestParam(value = "businessId", required = false) String businessId) throws Exception {
		Result<?> result = new Result<>();

		// 检查文件是否存在
		if (file == null || file.isEmpty()) {
			result.setMessage("文件不能为空");
			result.setSuccess(false);
			return result;
		}
		InputStream stream = file.getInputStream();
		String key = UUIDGenerator.generate();
		s3Util.uploadFile(key, stream);
		String savePath = s3Util.getFileUrl(key);
		if (oConvertUtils.isNotEmpty(savePath)) {
			OssFile ossFile = new OssFile();
			ossFile.setFileName(CommonUtils.getFileName(file.getName()));
			ossFile.setFileType(fileType);
			ossFile.setSuffix(Objects.requireNonNull(file.getOriginalFilename())
					.substring(file.getOriginalFilename().lastIndexOf(".") + 1));
			ossFile.setUrl(savePath);
			ossFile.setStorageType("s3");
			ossFile.setBusinessId(businessId);
			ossFileService.save(ossFile);
			result.setMessage(savePath);
			result.setSuccess(true);

		} else {
			result.setMessage("上传失败！");
			result.setSuccess(false);
		}
		return result;
	}

    // ======== 导出辅助方法 ========
    private String normalizeFieldName(String field) {
        if (field == null) {return null;}
        String f = field.trim();
        if (f.isEmpty()) {return null;}
        // 去除包裹反引号
        if (f.startsWith("`") && f.endsWith("")) { // 无效判断占位
            // do nothing
        }
        if (f.startsWith("`") && f.endsWith("`")) {
            f = f.substring(1, f.length()-1);
        }
        // snake_case -> camelCase
        if (f.contains("_")) {
            StringBuilder sb = new StringBuilder();
            String[] parts = f.split("_");
            for (int i=0;i<parts.length;i++) {
                String p = parts[i];
                if (i==0) { sb.append(p.toLowerCase()); }
                else if (!p.isEmpty()) { sb.append(Character.toUpperCase(p.charAt(0))).append(p.substring(1).toLowerCase()); }
            }
            f = sb.toString();
        }
        return f;
    }

    private Object normalizeJsonObjectToString(Object obj) {
        if (obj == null) { return null; }
        if (obj instanceof String) {
            String s = (String) obj;
            // 去除包裹的反引号与多余空格
            s = trimBackticks(s);
            return s;
        }
        return JSON.toJSONString(obj);
    }

    private String trimBackticks(String s) {
        if (s == null) {
            return null;
        }
        String r = s.trim();
        if (r.startsWith("`") && r.endsWith("`")) {
            r = r.substring(1, r.length()-1).trim();
        }
        return r;
    }

    private String cleanUrlString(String s) {
        if (s == null) {
            return null;
        }
        return trimBackticks(s).trim();
    }

    private String extractCoverPicFromGoodsPic(Object goodsPicObj) {
        if (goodsPicObj == null) { return null; }
        try {
            Map<String, Object> jsonMap;
            if (goodsPicObj instanceof String) {
                jsonMap = JSON.parseObject((String) goodsPicObj, Map.class);
            } else {
                jsonMap = JSON.parseObject(JSON.toJSONString(goodsPicObj), Map.class);
            }
            if (jsonMap != null && jsonMap.containsKey("coverPic")) {
                Object cv = jsonMap.get("coverPic");
                if (cv != null) {
                    return cleanUrlString(String.valueOf(cv));
                }
            }
        } catch (Exception e) {
            log.warn("提取封面图失败: {}", e.getMessage());
        }
        return null;
    }

    /**
     * 获取字段标题
     * @param fieldName 字段名
     * @param fieldTitleList 字段标题映射
     * @return 字段标题
     */
    private String getFieldTitle(String fieldName, List<String> fieldTitleList, List<String> fields) {
        // 如果有字段标题列表且与字段列表对应，则使用字段标题列表
        if (fieldTitleList != null && fields != null && fields.contains(fieldName)) {
            int index = fields.indexOf(fieldName);
            if (index >= 0 && index < fieldTitleList.size()) {
                return fieldTitleList.get(index);
            }
        }
        // 否则使用注解中的标题
        return getFieldDisplayName(fieldName);
    }

	/**
	 * 处理JSON字段查询条件
	 *
	 * @param queryWrapper 查询包装器
	 * @param parameterMap 参数映射
	 */
	private void processJsonFieldQueries(QueryWrapper<Goods> queryWrapper, Map<String, String[]> parameterMap) {
		for (Map.Entry<String, String[]> entry : parameterMap.entrySet()) {
			String paramName = entry.getKey();
			String[] paramValues = entry.getValue();

			// 检查是否为JSON字段查询格式 (fieldName_propertyName)
			if (paramName.contains("_") && paramValues != null && paramValues.length > 0 && StringUtils.isNotBlank(paramValues[0])) {
				String[] parts = paramName.split("_");
				if (parts.length >= 2) {
					String fieldName = parts[0];  // 如: goodsInfo

					// 检查是否为有效的JSON字段
					if (isValidJsonField(fieldName)) {
						String propertyName = parts[1];  // 如: brand
						// 将驼峰命名转换为下划线命名
						fieldName = camelToUnderline(fieldName);

						String operator = "=";  // 默认操作符
						String value = paramValues[0];

						// 解析操作符
						if (parts.length >= 3) {
							if ("like".equalsIgnoreCase(parts[2])) {
								operator = "LIKE";
								// 使用QueryWrapper的like方法而不是直接构建SQL
								queryWrapper.apply(fieldName + "->>'$." + propertyName + "' " + operator + " {0}", "%" + value + "%");
							} else if ("min".equalsIgnoreCase(parts[2])) {
								operator = ">=";
								queryWrapper.apply(fieldName + "->>'$." + propertyName + "' " + operator + " {0}", value);
							} else if ("max".equalsIgnoreCase(parts[2])) {
								operator = "<=";
								queryWrapper.apply(fieldName + "->>'$." + propertyName + "' " + operator + " {0}", value);
							}
						} else {
							queryWrapper.apply(fieldName + "->>'$." + propertyName + "' " + operator + " {0}", value);
						}
					}
				}
			}
		}
	}

	/**
	 * 驼峰命名转下划线命名
	 * @param camelCaseName 驼峰命名
	 * @return 下划线命名
	 */
	private String camelToUnderline(String camelCaseName) {
		if (camelCaseName == null || camelCaseName.isEmpty()) {
			return camelCaseName;
		}
		StringBuilder result = new StringBuilder();
		result.append(Character.toLowerCase(camelCaseName.charAt(0)));
		for (int i = 1; i < camelCaseName.length(); i++) {
			char c = camelCaseName.charAt(i);
			if (Character.isUpperCase(c)) {
				result.append("_");
				result.append(Character.toLowerCase(c));
			} else {
				result.append(c);
			}
		}
		return result.toString();
	}

	/**
	 * 验证是否为有效的JSON字段
	 *
	 * @param fieldName 字段名
	 * @return 是否为有效JSON字段
	 */
	private boolean isValidJsonField(String fieldName) {
		// 定义有效的JSON字段列表
		Set<String> jsonFields = new HashSet<>(Arrays.asList(
			"goods_info", "goods_price", "goods_sale", "goods_english", "goods_deliver", "goods_extend"
		));

		return jsonFields.contains(fieldName);
	}
}
